
package com.huaweicloud.haydn.delivercore.agent.service.metadata.impl;

import com.huaweicloud.haydn.delivercore.agent.common.exception.BusinessErrorEnum;
import com.huaweicloud.haydn.delivercore.agent.common.exception.BusinessException;
import com.huaweicloud.haydn.delivercore.agent.dto.excel.DbExcelDto;
import com.huaweicloud.haydn.delivercore.agent.dto.excel.FieldEmptyExcelDto;
import com.huaweicloud.haydn.delivercore.agent.dto.excel.FieldExcelDto;
import com.huaweicloud.haydn.delivercore.agent.dto.excel.TableExcelDto;
import com.huaweicloud.haydn.delivercore.agent.service.CommonUtils;

import lombok.extern.slf4j.Slf4j;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @since 2022-09-26
 */
@Slf4j
@Service("mysql")
public class MysqlDatabaseUtil extends BaseDatabaseUtil {

    @Autowired
    private CommonUtils commonUtils;

    @Override
    public String getDriverClass() {
        return "org.mariadb.jdbc.Driver";
    }

    @Override
    public String getAllDatabasesSql() {
        return "select TABLE_SCHEMA as db_name,count(concat(round((data_length+index_length)/1024/1024,2),'MB')) as db_table_num,"
            + "concat(truncate(sum(data_length)/1024/1024,2),'MB') as db_capacity,VERSION()  from information_schema.tables group by TABLE_SCHEMA ";
    }

    @Override
    public String getSelectedDatabasesSql(String dbNames) throws BusinessException {
        return "select TABLE_SCHEMA as db_name,count(concat(round((data_length+index_length)/1024/1024,2),'MB')) as db_table_num,"
            + "concat(truncate(sum(data_length)/1024/1024,2),'MB') as db_capacity,VERSION() from information_schema.tables "
            + " where table_schema in " + dbNames + " group by TABLE_SCHEMA ";
    }

    @Override
    public String getAllTablesSql() {
        return "select table_schema as db_name, count(concat(round((data_length+index_length)/1024/1024,2),'MB')) as "
            + "db_table_num, concat(truncate(sum(data_length)/1024/1024,2),'MB') as db_capacity,"
            + "table_name as 'table_name', table_comment as 'table_notes', table_rows as 'table_row_number', avg_row_length "
            + "as 'table_avg_length',  truncate(data_length/1024/1024, 2) as 'table_phy_capacity', table_rows*avg_row_length as "
            + "'table_logical_capacity' from information_schema.tables group by table_name order by db_name ";
    }

    @Override
    public String getAllTablesByDbSql(String dbNames) {
        return "select table_schema as db_name, count(concat(round((data_length+index_length)/1024/1024,2),'MB')) as "
            + "db_table_num, concat(truncate(sum(data_length)/1024/1024,2),'MB') as db_capacity,"
            + "table_name as 'table_name', table_comment as 'table_notes', table_rows as 'table_row_number', avg_row_length "
            + "as 'table_avg_length',  truncate(data_length/1024/1024, 2) as 'table_phy_capacity', table_rows*avg_row_length as "
            + "'table_logical_capacity' from information_schema.tables where table_schema in " + dbNames
            + " group by table_name order by db_name ";
    }

    @Override
    public String getSelectedTableSql(String dbNames, String tableNames) {
        return "select table_schema as db_name, count(concat(round((data_length+index_length)/1024/1024,2),'MB')) as "
            + "db_table_num, concat(truncate(sum(data_length)/1024/1024,2),'MB') as db_capacity,"
            + "table_name as 'table_name', table_comment as 'table_notes', table_rows as 'table_row_number', avg_row_length "
            + "as 'table_avg_length',  truncate(data_length/1024/1024, 2) as 'table_phy_capacity', table_rows*avg_row_length as "
            + "'table_logical_capacity' from information_schema.tables where table_schema in " + dbNames
            + " and table_name in " + tableNames + " group by table_name order by db_name ";
    }

    @Override
    public String getAllFieldsSql() {
        return "SELECT table_schema as db_name, table_name, column_name as table_name_en, column_comment as field_name_zh, "
            + "data_type as field_type, character_maximum_length as field_len, "
            + "column_comment as field_notes, column_key as is_primary_key, is_nullable as is_nullable "
            + "FROM information_schema.columns order BY TABLE_NAME ";
    }

    @Override
    public String getAllFieldsByDbSql(String dbNames) {
        return "SELECT table_schema as db_name, table_name, column_name as table_name_en, column_comment as field_name_zh, "
            + "data_type as field_type, character_maximum_length as field_len, "
            + "column_comment as field_notes, column_key as is_primary_key, is_nullable as is_nullable "
            + "FROM information_schema.columns where table_schema in " + dbNames + " order BY TABLE_NAME ";
    }

    @Override
    public String getAllFieldsSql(String dbNames, String tableNames) {
        return "SELECT table_schema as db_name, table_name, column_name as table_name_en, column_comment as field_name_zh, "
            + "data_type as field_type, character_maximum_length as field_len, "
            + "column_comment as field_notes, column_key as is_primary_key, is_nullable as is_nullable "
            + "FROM information_schema.columns where table_schema in " + dbNames + " and table_name in " + tableNames
            + " order BY TABLE_NAME ";
    }

    @Override
    public String getURL(String hostname, String port, String databaseName) {
        return "jdbc:mariadb://" + hostname + ":" + port + "/" + databaseName
            + "?serverTimezone=UTC&connectTimeout=60000&socketTimeout=60000";
    }

    @Override
    public String getURLException(String hostname, String port, String databaseName) {
        return "jdbc:mariadb://" + hostname + ":" + port + "/" + databaseName
            + "?serverTimezone=UTC&connectTimeout=60000&socketTimeout=60000";
    }

    @Override
    public List<DbExcelDto> getAllDbs(String datalinkName, String dbNames, String schemas) {
        List<DbExcelDto> result = new ArrayList<>();
        try (Connection conn = getConnection()) {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
            String realDbNames = commonUtils.sqlQueryBuild(dbNames);
            String sql =
                StringUtils.isNotBlank(realDbNames) ? getSelectedDatabasesSql(realDbNames) : getAllDatabasesSql();
            try (PreparedStatement ps =
                conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
                try (ResultSet rs = ps.executeQuery()) {
                    String today = sdf.format(new Date());
                    while (rs.next()) {
                        // 获取数据表中满足要求的一行数据，并放入Map中
                        DbExcelDto dbExcelDto = new DbExcelDto();
                        dbExcelDto.setDatabaseName(rs.getString(1));
                        dbExcelDto.setDatabaseTableNum(rs.getString(2));
                        dbExcelDto.setDatabaseCapacity(rs.getString(3));
                        dbExcelDto.setVersion(rs.getString(4));
                        dbExcelDto.setDatalinkName(datalinkName);
                        dbExcelDto.setGenTime(today);
                        result.add(dbExcelDto);
                    }
                }
            }
            return result;
        } catch (SQLException e) {
            log.error(e.getMessage());
            throw new BusinessException(BusinessErrorEnum.BASE_INTERNAL_SERVER_ERROR);
        }
    }

    @Override
    public List<TableExcelDto> getAllTables(String datalinkName, String dbNames, String schemas, String tableNames) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        Connection conn = null;
        List<TableExcelDto> result = new ArrayList<>();
        try {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
            String today = sdf.format(new Date());
            String sql;
            String realDbNames = commonUtils.sqlQueryBuild(dbNames);
            String realTableNames = commonUtils.sqlQueryBuild(tableNames);
            if (StringUtils.isNotBlank(realDbNames) && StringUtils.isNotBlank(realTableNames)) {
                sql = getSelectedTableSql(realDbNames, realTableNames);
            } else if (StringUtils.isBlank(realDbNames) && StringUtils.isBlank(realTableNames)) {
                sql = getAllTablesSql();
            } else if (StringUtils.isNotBlank(realDbNames) && StringUtils.isBlank(realTableNames)) {
                sql = getAllTablesByDbSql(realDbNames);
            } else {
                throw new BusinessException(BusinessErrorEnum.BASE_INTERNAL_SERVER_ERROR,
                    "db.names and table.names must all be configured");
            }

            conn = getConnection();
            ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            rs = ps.executeQuery();
            while (rs.next()) {
                // 获取数据表中满足要求的一行数据，并放入Map中
                TableExcelDto tableExcelDto = new TableExcelDto();
                tableExcelDto.setGenTime(today);
                tableExcelDto.setDatalinkName(datalinkName);
                tableExcelDto.setDatabaseName(rs.getString(1));
                tableExcelDto.setDatabaseTableNum(rs.getString(2));
                tableExcelDto.setDatabaseCapacity(rs.getString(3));
                tableExcelDto.setTableName(rs.getString(4));
                tableExcelDto.setTableDescription(rs.getString(5));
                tableExcelDto.setTableRows(rs.getString(6));
                tableExcelDto.setTableAvgLength(rs.getString(7));
                tableExcelDto.setTablePhysicsCapacity(rs.getString(8));
                tableExcelDto.setTableCapacity(rs.getString(9));
                result.add(tableExcelDto);
            }
            return result;
        } catch (SQLException e) {
            log.error(e.getMessage());
            throw new BusinessException(BusinessErrorEnum.BASE_INTERNAL_SERVER_ERROR);
        } finally {
            close(rs, ps, conn);
        }
    }

    @Override
    public List<FieldExcelDto> getAllFields(String datalinkName, String dbNames, String schemas, String tableNames) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        Connection conn = null;
        List<FieldExcelDto> result = new ArrayList<>();
        try {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
            String today = sdf.format(new Date());
            String sql;
            String realDbNames = commonUtils.sqlQueryBuild(dbNames);
            String realTableNames = commonUtils.sqlQueryBuild(tableNames);
            if (StringUtils.isNotBlank(realDbNames) && StringUtils.isNotBlank(realTableNames)) {
                sql = getAllFieldsSql(realDbNames, realTableNames);
            } else if (StringUtils.isBlank(realDbNames) && StringUtils.isBlank(realTableNames)) {
                sql = getAllFieldsSql();
            } else if (StringUtils.isNotBlank(realDbNames) && StringUtils.isBlank(realTableNames)) {
                sql = getAllFieldsByDbSql(realDbNames);
            } else {
                throw new BusinessException(BusinessErrorEnum.BASE_INTERNAL_SERVER_ERROR,
                    "db.names and table.names must all be configured");
            }
            conn = getConnection();
            ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            rs = ps.executeQuery();
            while (rs.next()) {
                FieldExcelDto fieldExcelDto = new FieldExcelDto();
                fieldExcelDto.setGenTime(today);
                fieldExcelDto.setDatalinkName(datalinkName);
                fieldExcelDto.setDatabaseName(rs.getString(1));
                fieldExcelDto.setTableName(rs.getString(2));
                fieldExcelDto.setFieldNameEn(rs.getString(3));
                fieldExcelDto.setFieldNameZh(rs.getString(4));
                fieldExcelDto.setFieldType(rs.getString(5));
                fieldExcelDto.setFieldLength(rs.getString(6));
                fieldExcelDto.setFieldDescription(rs.getString(7));
                if ("PRI".equals(rs.getString(8))) {
                    fieldExcelDto.setIsPrimaryKey("YES");
                }
                fieldExcelDto.setIsNullable(rs.getString(9));
                result.add(fieldExcelDto);
            }
            return result;
        } catch (SQLException e) {
            throw new BusinessException(BusinessErrorEnum.BASE_INTERNAL_SERVER_ERROR);
        } finally {
            close(rs, ps, conn);
        }
    }

    @Override
    public List<FieldEmptyExcelDto> getAllEmptyRateFields(String datalinkName, String dbNames, String schemas,
        Map<String, List<String>> fieldTableMap) {
        log.info("todo");
        return null;
    }

}
